IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDescendants]') AND type in (N'FN', N'TF'))
DROP FUNCTION [dbo].[GetDescendants]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****************************************************************************************          
* Name:   GetDescendants          
* Purpose:  Retrieves the parent and child relationship for the passed in admpersonId            
*          
* PARAMETERS          
* Name						Description               
* -------------      ------------------------------------------------------------------------         
* @admPersonId				Domain User ID (REQUIRED)            
* @recursive				Determines whether or to fetch all the descendants or Just the Direct Reports
							1 = All the descendantds Recursively
							0 = Only the Direct Reports       
* @isRelationExisting       Determines if we have to consider the existing relations or existing and past relations.
							1 ==> existing and past relationships. [Removed = 0 ro 1]
							0 ==> existing relationships. [Removed = 0]    
*******************************************************************************************/          
CREATE Function dbo.GetDescendants     
(        
 @admPersonId int,        
 @recursive bit,      
 @isRelationExisting bit,
 @HierarchyTypeId int = 0      
)        

RETURNS @@descendants TABLE (ChildId INT,ParentId INT,HierarchyTypeId INT)                    
       
AS BEGIN        
	
	WITH RecursiveHierarchy (ChildId,ParentId,HierarchyTypeId,Level) AS
	(
		SELECT ChildId,ParentId,HierarchyTypeId,0 AS Level
		FROM COI_Hierarchy_Map
		WHERE
--		ChildId = ParentId --Ensures that the parent is at the top most level in the hierarchy.
--		AND [Commented the first clause b'cos any supervisor who doesn't have childId = ParentId return no rows.
		ChildId = @admPersonId
		--12/31/09 - AND Removed = 0 is commented as some of the Higher ups in the Hierarchy are Inactive but need to look pople under them.
--		AND Removed = 0 -- This ensures the parent is Active/Not removed.
--		AND HierarchyTypeId = (CASE @HierarchyTypeId
--								  WHEN 0 THEN HierarchyTypeId
--								  ELSE @HierarchyTypeId 
--							  END) --This is filtering the Parent by the hierarchy Id.
		UNION ALL	
		SELECT COHMap.ChildId,COHMap.ParentId,COHMap.HierarchyTypeId,Level + 1
		FROM COI_Hierarchy_Map COHMap 
			INNER JOIN RecursiveHierarchy rs ON COHMap.ParentId = rs.ChildID
		WHERE
		COHMap.ChildId != rs.ChildId
		AND COHMap.Removed = (CASE @isRelationExisting
								WHEN 0 THEN @isRelationExisting
								ELSE COHMap.Removed 
							  END)
		AND COHMap.HierarchyTypeId = (CASE @HierarchyTypeId
										  WHEN 0 THEN COHMap.HierarchyTypeId
										  ELSE @HierarchyTypeId 
									  END) 	
		AND rs.[Level] < (CASE @recursive
								WHEN 0 THEN 1
								ELSE rs.[Level]+1
							   END)
	)
	INSERT INTO @@descendants
	SELECT ChildId,ParentId,HierarchyTypeId FROM RecursiveHierarchy
	WHERE ChildId != @admPersonId --Eliminating the passed in user
	Order By ChildID
	
RETURN

END      








--select * from  [dbo].[GetDescendants](@admPersonId=263550,@recursive=1,@isRelationExisting=0,@HierarchyTypeId=0)
--select * from  [dbo].[GetDescendants](208960,0,0,0)